package com.fanfeifei.web.dao;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

public class BaseDao
{
    private final static Logger logger = LoggerFactory.getLogger(BaseDao.class);

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate()
    {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    public <T> T queryRawSQL(String sql, ResultSetExtractor<T> extactor)
    {
        return jdbcTemplate.query(sql, extactor);
    }

    private List<ColDef> extractColDef(ResultSetMetaData rsm)
            throws SQLException
    {
        // get col defination
        int colCount = rsm.getColumnCount();
        List<ColDef> colDefList = new ArrayList<ColDef>();
        for (int now = 1; now <= colCount; now++)
        {
            String colClzName = rsm.getColumnClassName(now);
            String colLabel = rsm.getColumnLabel(now);
            String colName = rsm.getColumnName(now);
            String colType = rsm.getColumnTypeName(now);

            ColDef def = new ColDef(colLabel, colClzName, now);
            def.setColName(colName);
            def.setColSQLType(colType);
            colDefList.add(def);
            if (logger.isDebugEnabled())
            {
                logger.debug(def.toString());
            }
        }
        return colDefList;
    }

    /**
     * this with mapping DB colName to a POJO field with the rule COL_NAME_EXAM
     * -> colNameExam and invoke it's set method
     * setColNameExam(java.class.stringname)
     * 
     * @param <T>
     * @param <CLS>
     *            this class need a public non-parameter constructor
     * @param sql
     * @param elemClz
     *            class object
     * @return
     */
    public <T, CLS extends Class> List<T> queryRawSQL(final String sql,
            final CLS elemClz)
    {
        return jdbcTemplate.query(sql, new ResultSetExtractor<List<T>>()
        {

            @SuppressWarnings("unchecked")
            public List<T> extractData(ResultSet rs) throws SQLException,
                    DataAccessException
            {

                List<ColDef> colDefList = extractColDef(rs.getMetaData());

                List<T> result = new ArrayList<T>();

                while (rs.next())
                {
                    ColDef def = null;
                    Class<?> clz = elemClz;
                    Object inst = null;
                    try
                    {
                        inst = clz.newInstance();
                    }
                    catch (Exception e1)
                    {
                        logger.error(String
                                .format("###### Can't create instance for class[%s],no non-parameter constructor is availabel ######",
                                        elemClz));

                        throw new RuntimeException(
                                String.format(
                                        "###### Can't create instance for class[%s],no non-parameter constructor is availabel ######",
                                        elemClz));
                    }

                    result.add((T) inst);
                    for (int now = 0; now < colDefList.size(); now++)
                    {
                        def = colDefList.get(now);
                        Object colVal = rs.getObject(def.getColName());

                        Method mthd;
                        try
                        {
                            mthd = clz.getMethod(def.getSetMethodName(),
                                    def.getColClz());
                            mthd.invoke(inst, colVal);
                        }
                        catch (SecurityException e)
                        {
                            e.printStackTrace();
                        }
                        catch (NoSuchMethodException e)
                        {
                            logger.error(String
                                    .format("###### The Method [%s.%s(%s)] is not found ######",
                                            elemClz, def.getSetMethodName(),
                                            def.getColClz()));
                        }
                        catch (IllegalArgumentException e)
                        {
                            e.printStackTrace();
                        }
                        catch (IllegalAccessException e)
                        {
                            e.printStackTrace();
                        }
                        catch (InvocationTargetException e)
                        {
                            e.printStackTrace();
                        }

                    }
                }
                return result;
            }

        });

    }

    public static class ColDef
    {
        public static final Pattern p = Pattern.compile("_(\\w?)");
        private int colIndex;
        private String colLabel;
        private String colName;
        private String colSQLType;
        private String colJavaType;
        private String fieldName;

        private Class colClz;

        public ColDef(String colLabel, String colJavaType)
        {
            super();
            this.setColLabel(colLabel);
            this.setColJavaType(colJavaType);
        }

        public ColDef(String colLabel, String colJavaType, int colIndex)
        {
            super();
            this.setColLabel(colLabel);
            this.setColJavaType(colJavaType);
            this.colIndex = colIndex;
        }

        public static String colName2FieldName(String col)
        {
            Matcher m = p.matcher(col.toLowerCase());
            StringBuffer sb = new StringBuffer();
            while (m.find())
            {
                String gp1 = m.group(1);
                m.appendReplacement(sb, gp1.toUpperCase());
            }
            m.appendTail(sb);
            return sb.toString();
        }

        public String getFieldName()
        {
            return fieldName;
        }

        public String getSetMethodName()
        {
            return "set".concat(fieldName.substring(0, 1).toUpperCase())
                    .concat(fieldName.substring(1));
        }

        public String getColLabel()
        {
            return colLabel;
        }

        public void setColLabel(String colLabel)
        {
            this.colLabel = colLabel;
            this.fieldName = colName2FieldName(colLabel);

        }

        public String getColName()
        {
            return colName;
        }

        public void setColName(String colName)
        {
            this.colName = colName;
        }

        public String getColSQLType()
        {
            return colSQLType;
        }

        public void setColSQLType(String colSQLType)
        {
            this.colSQLType = colSQLType;
        }

        public String getColJavaType()
        {
            return colJavaType;
        }

        public void setColJavaType(String colJavaType)
        {
            this.colJavaType = colJavaType;
            try
            {
                this.colClz = Class.forName(colJavaType);
            }
            catch (ClassNotFoundException e)
            {
                e.printStackTrace();
                logger.error(e.getMessage());
            }
        }

        public int getColIndex()
        {
            return colIndex;
        }

        public void setColIndex(int colIndex)
        {
            this.colIndex = colIndex;
        }

        public Class getColClz()
        {
            return colClz;
        }

        @Override
        public String toString()
        {
            return String
                    .format("ColDef [colIndex=%s, colLabel=%s, colName=%s, colSQLType=%s, colJavaType=%s, fieldName=%s]",
                            colIndex, colLabel, colName, colSQLType,
                            colJavaType, fieldName);
        }

    }
}
